﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using QUANLIHOCSINH.BussinessObject;
using QUANLIHOCSINH.DataAccess;
using System.Windows.Forms;
namespace QUANLIHOCSINH.BussinessLogic
{
    class NGUOIDUNGBUS
    {

        ConnectData dataConn = new ConnectData();
        public DataTable LayDanhSachNguoiDungThuong()
        {
            
            return dataConn.GetData("select *  from  NguoiDung where MaNhom in ('3','4')");
        }
        public NGUOIDUNG LayThongTinNguoiDung(string tendangnhap, string matkhau)
        {
            string sql = "select * from NguoiDung as ND   LEFT JOIN NhomNguoiDung as NND ON ND.MaNhom = NND.MaNhom";
            sql += " Where MaNguoiDung ='" + tendangnhap + "' and MatKhau ='" + matkhau + "'";
            DataTable tbUser = dataConn.GetData(sql);
            NGUOIDUNG user = new NGUOIDUNG();
            if (tbUser.Rows.Count > 0)
            {
                DataRow rowUser = tbUser.Rows[0];
                user.MaNguoiDung = rowUser["MaNguoiDung"].ToString();
                user.MatKhau = rowUser["MatKhau"].ToString();
                user.TenNguoiDung = rowUser["TenNguoiDung"].ToString();
                user.TenNhom = rowUser["TenNhom"].ToString();
                user.MaNhom = rowUser["MaNhom"].ToString();
            }
            else
            {
                user.MaNguoiDung = "";
                user.MatKhau = "";
                user.TenNguoiDung = "";
                user.TenNhom = "";
                user.MaNhom = "";
            }
            return user;
        }
        public bool checklogin(string tendangnhap, string matkhau)
        {
            string sql = "select * from NguoiDung where MaNguoiDung ='" + tendangnhap + "' and MatKhau ='" + matkhau + "'";
            DataTable tbUser = dataConn.GetData(sql);
            NGUOIDUNG user = new NGUOIDUNG();
            if (tbUser.Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        public DataTable LayDanhSachGiaoVien()
        {
        
            return dataConn.GetData("select *  from  NguoiDung where MaNhom = 4");
        }
        public DataTable LayDanhSachGiaoVienChuaPhanCong()
        {
            return dataConn.GetData("select *  from  NguoiDung where MaNhom = 4 and  MaNguoiDung  not in ( select MaGV  from LopHoc)");
        }
        
        public string NextID()
        {
            // Neu Chua Du Lieu Thi Mac Dinh Khoi Tao La HS001
            if (dataConn.GetLastID("nguoidung", "manguoidung").Equals(""))
            {
                return "ND001";
            }
            else
            {
                return Utility.LayMaTuDong(dataConn.GetLastID("nguoidung", "manguoidung"), "ND");
            }
        }
        public bool KiemTraNguoiDung(NGUOIDUNG hs)
        {
            if (hs.TenNguoiDung.Equals(""))
            {
                MessageBox.Show("Tên Người Dùng Không Hợp Lệ");
                return false;
            }

            if (hs.MatKhau.Equals(""))
            {
                MessageBox.Show("Mật Khẩu Không Hợp Lệ");
                return false;
            }

            if (hs.DiaChi.Equals(""))
            {
                MessageBox.Show("Địa Chỉ Không Hợp Lệ");
                return false;
            }

            return true;
        }
        public bool ThemNguoiDung(NGUOIDUNG hs)
        {
            if (KiemTraNguoiDung(hs))
            {
                string sql = string.Format("INSERT INTO NGUOIDUNG (manguoidung,matkhau,tennguoidung,gioitinh,diachi,manhom)  VALUES ('{0}','{1}','{2}','{3}','{4}','{5}')", hs.MaNguoiDung, hs.MatKhau, hs.TenNguoiDung, hs.GioiTinh, hs.DiaChi, hs.MaNhom);
                if (dataConn.ExecuteQuery(sql))
                {
                    MessageBox.Show("Thêm Học Sinh Thàng Công", "Thông Tin", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return true;
                }
            }
            return false;
        }
        public void XoaNguoiDung(NGUOIDUNG hs)
        {
          
                string sql = string.Format("DELETE  FROM NGuoiDung Where manguoidung = '{0}'", hs.MaNguoiDung);
                if (dataConn.ExecuteQuery(sql))
                {
                    MessageBox.Show("Thêm Học Sinh Thàng Công", "Thông Tin", MessageBoxButtons.OK, MessageBoxIcon.Information);

                }
            
        }
        public bool SuaNguoiDung(NGUOIDUNG hs)
        {
            if (KiemTraNguoiDung(hs))
            {
                if (KiemTraChuNhiem(hs) == false)
                {
                    string sql = string.Format("UPDATE NGUOIDUNG SET matkhau = '{0}' ,  tennguoidung = N'{1}' , gioitinh = '{2}' , manhom = '{3}' , diachi = N'{4}' where manguoidung = '{5}'", hs.MatKhau, hs.TenNguoiDung, hs.GioiTinh, hs.MaNhom, hs.DiaChi, hs.MaNguoiDung);
                    if (dataConn.ExecuteQuery(sql))
                    {
                        MessageBox.Show("Cập Nhật Học Sinh Thàng Công", "Thông Tin", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        return true;
                    }
                }
                else
                {

                    string sql = string.Format("UPDATE NGUOIDUNG SET matkhau = '{0}' ,  tennguoidung = N'{1}' , gioitinh = '{2}', diachi = N'{3}' where manguoidung = '{4}'", hs.MatKhau, hs.TenNguoiDung, hs.GioiTinh, hs.DiaChi, hs.MaNguoiDung);
                    if (dataConn.ExecuteQuery(sql))
                    {
                        MessageBox.Show("Cập Nhật Học Sinh Thàng Công", "Thông Tin", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        return true;
                    }

                }
            }
            return false;
        }
        public bool KiemTraChuNhiem(NGUOIDUNG hs)
        {
            string sql = string.Format("select * from lophoc where magv = '{0}'", hs.MaNguoiDung);
            DataTable tbUser = dataConn.GetData(sql);
            if (tbUser.Rows.Count > 0)
            {
                return true;

            }
            return false;
        }
    }
}
